﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DataAccessLayer.Mapper;
using DataAccessLayer.Models.POJO;
using MySql.Data.MySqlClient;

namespace DataAccessLayer.Service
{
    public class departmentService : departmentMapper
    {
        public static MySqlConnection connection = null;

        public void create()
        {
            connection = new MySqlConnection("data source = localhost; database = dotnet; user id = root; password = 123456; pooling = true; Charset=utf8; ");
        }
        public void Open()
        {
            connection.Open();
        }

        public void Close()
        {
            connection.Close();
        }
        public bool Delete(departmentPeta Info)
        {
            create();
            var db = new PetaPoco.Database(connection);
            Open();
            if (db.Delete<userInfoPeta>("where deptHostId = @0 and deptId = @1 ", Info.deptHostId,Info.deptId) > 0)
            {
                Close();
                return true;
            }
            else
            {
                Close();
                return false;
            }
        }

        public bool Insert(departmentPeta userInfo)
        {
            create();
            var db = new PetaPoco.Database(connection);
            Open();
            db.Insert(userInfo);
            Close();
            return true;
        }

        public List<departmentPeta> SelectAll()
        {
            create();
            var db = new PetaPoco.Database(connection);
            Open();
            List<departmentPeta> list = db.Query<departmentPeta>("select * from department03").ToList();
            Close();
            return list;
        }

        public departmentPeta SelectSingleInfoPetaById(string Id)
        {
            create();
            var db = new PetaPoco.Database(connection);
            Open();
            String sql = "select * from department03 where deptId = '{0}'";
            sql = String.Format(sql, Id);
            List<departmentPeta> list = db.Query<departmentPeta>(sql).ToList();
            Close();
            if (list.Count != 0)
                return list[0];
            else return null;
        }

        public bool Update(departmentPeta Info)
        {
            create();
            var db = new PetaPoco.Database(connection);
            Open();
            if (db.Update<departmentPeta>("set deptHostId = @0 , deptNumber = @1 where deptId = @2", Info.deptHostId, Info.deptNumber, Info.deptId) > 0)
            {
                Close();
                return true;
            }
            else
            {
                Close();
                return false;
            }
        }

        

        public List<userInfoPeta> SelectAllEmployee(string deptId)
        {
            create();
            var db = new PetaPoco.Database(connection);
            Open();
            String sql = "select * from userinfo03 where deptId = '{0}'";
            sql = String.Format(sql, deptId);
            List<userInfoPeta> list = db.Query<userInfoPeta>(sql).ToList();
            Close();
            return list;
        }
    }
}
